package com.gjy.sql.dm;

import org.junit.Test;

import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author gjy
 * @version 1.0
 * @since 2025-01-10 18:10:42
 */
public class DmTest {

    @Test
    public void test15() {
        String sql = "select * from SERVICE_RECORD_DETAILS_120_INFORMATION_ENTERPRISE_CHANNEL where EXTRA_DATA_ID='2023031024401'";
        Connection connection = DmUtil.connection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String r1 = rs.getString("SETTLE_PROPORTION");
                BigDecimal r2 = rs.getBigDecimal("SETTLE_PROPORTION");
                double r3 = rs.getDouble("SETTLE_PROPORTION");
                System.out.println("r1 = " + r1);
                System.out.println("r2 = " + r2);
                System.out.println("r3 = " + r3);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test14() {
        String sql = "select * from SETTLE.CFG_ACCOUNT_BOOK where BUSINESS_PARENT_CODE = :BUSINESS_PARENT_CODE";
        Connection connection = DmUtil.connection();
        List<String> list = new ArrayList<>();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setObject(1,120);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String batchId = rs.getString("ACCOUNT_BOOK_NAME");
                list.add(batchId);
            }
            System.out.println(list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test13() {
        String sql = "select BATCH_ID from  SERVICE_RECORD_1122 where STATUS = 0 AND OPERATION_ID=?";
        Connection connection = DmUtil.connection();
        List<String> list = new ArrayList<>();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setLong(1, 10000538591L);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String batchId = rs.getString("BATCH_ID");
                list.add(batchId);
            }
            System.out.println(list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test12() {
        String sql = "insert into test_11 (a, b) values (?,?)";
        Connection connection = DmUtil.connection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setObject(1, 0.0);
            ps.setObject(2, 0.0);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test11() {
        String sql = "insert into SETTLE_RESULT_STAT_1123 (CUST_ID,CUST_TYPE,ACCOUNT_PERIOD,EXCL_TAX_SETTLE_FEE) values (?,?,?,?)";
        Connection connection = DmUtil.connection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setObject(1, "hello");
            ps.setObject(2, "1");
            ps.setObject(3, "202555");
            ps.setDouble(4, 0.0);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test10() {
        String sql = "select PUNISH_NUMBER,REWARD_NUMBER from SETTLE_RESULT_STAT_1123 where cust_id='103037-B' and ACCOUNT_PERIOD='202504'";
        Connection connection = DmUtil.connection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject("PUNISH_NUMBER"));
                System.out.println(rs.getObject("REWARD_NUMBER"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test9() {
        String sql = "select PUNISH_NUMBER,REWARD_NUMBER from SETTLE_RESULT_STAT_1123 where cust_id='103244' and ACCOUNT_PERIOD='202411'";
        Connection connection = DmUtil.connection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject("PUNISH_NUMBER"));
                System.out.println(rs.getObject("REWARD_NUMBER"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test8() throws Exception {
        String sql = "select * from SERVICE_RECORD_SOURCE_DETAILS_120 where ORIGINAL_UUID='25F1B3DE886A363AE063FB002E0AFB70'";
        Connection connection = DmUtil.connection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        if (rs.next()) {
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                if ("TIMESTAMP".equals(metaData.getColumnTypeName(i))) {
                    /*Timestamp timestamp = rs.getTimestamp(i);
                    if (null != timestamp) {
                        String format = sdf.format(timestamp);
                        System.out.println(format);
                    }*/
                    Date timestamp = rs.getDate(i);
                    if (null != timestamp) {
                        String format = sdf.format(timestamp);
                        System.out.println(format);
                    }
                } else {
                    /*Object value = rs.getObject(i);
                    System.out.println(columnName + ":" + value + ":" + metaData.getColumnTypeName(i));*/
                }
            }
        }
    }

    @Test
    public void test7() {
        String sql = "alter table LOGIN_SECRET drop column aa";
        Connection connection = DmUtil.connection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void test6() throws Exception {
        String sql = "select cust_id,cust_type,t.AUDIT_STATUS,t.FINANCIAL_PERIOD,t.* from settle.ENERGY_FEE t where t.ACCOUNT_PERIOD=202501";
        Connection connection = DmUtil.connection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            String tbsj = rs.getString("cust_id");
            System.out.println(tbsj);
        }
    }

    @Test
    public void test1() throws Exception {
        String sql = "select * from cfg_settle_app";
        Connection connection = DmUtil.connection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            String tbsj = rs.getString("code");
            System.out.println(tbsj);
        }
    }

    @Test
    public void test2() throws Exception {
        String sql = "SELECT COUNT(DISTINCT TASK_ID) C FROM CFG_SETTLE_COLLECT_TASK_APPROVE";
        Connection connection = DmUtil.connection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            Object tbsj = rs.getObject("C");
            System.out.println(tbsj);
        }
    }

    @Test
    public void test3() throws Exception {
        String sql = "select STATUS from DISTRIBUTED_FILE_INFO_HIS";
        Connection connection = DmUtil.connection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            Object tbsj = rs.getObject("STATUS");
            System.out.println(tbsj);
        }
    }

    @Test
    public void test4() throws Exception {
        String sql = "select cast(count(distinct TASK_ID) as NUMBER) c from CFG_SETTLE_COLLECT_TASK_approve";
        Connection connection = DmUtil.connection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            Object tbsj = rs.getObject("c");
            System.out.println(tbsj);
        }
    }

    @Test
    public void test5() throws Exception {
        String sql = "merge into energy_fee a\n" +
                "using (select account_period,\n" +
                "              id,\n" +
                "              channel_type,\n" +
                "              channel_name,\n" +
                "              supply_name,\n" +
                "              contract_amount,\n" +
                "              contract_city_id,\n" +
                "              contract_city_name,\n" +
                "              contract_code,\n" +
                "              contract_province_id,\n" +
                "              contract_province_name,\n" +
                "              contract_title,\n" +
                "              dept_code,\n" +
                "              dept_name,\n" +
                "              business_code,\n" +
                "              business_name,\n" +
                "              invoice_type,\n" +
                "              org_code,\n" +
                "              org_name,\n" +
                "              profit_center_code,\n" +
                "              profit_center_name,\n" +
                "              supply_code\n" +
                "       from channel\n" +
                "       where id = '102666') b\n" +
                "on (a.CUST_ID = b.ID and a.account_period = b.account_period and a.cust_type = 1)\n" +
                "when matched then\n" +
                "    update\n" +
                "    set a.supply_name            = b.supply_name,\n" +
                "        a.contract_amount        = b.contract_amount,\n" +
                "        a.contract_city_id       = b.contract_city_id,\n" +
                "        a.contract_city_name     = b.contract_city_name,\n" +
                "        a.contract_code          = b.contract_code,\n" +
                "        a.contract_province_id   = b.contract_province_id,\n" +
                "        a.contract_province_name = b.contract_province_name,\n" +
                "        a.contract_title         = b.contract_title,\n" +
                "        a.dept_code              = b.dept_code,\n" +
                "        a.dept_name              = b.dept_name,\n" +
                "        a.business_code          = b.business_code,\n" +
                "        a.business_name          = b.business_name,\n" +
                "        a.invoicetype            = b.invoice_type,\n" +
                "        a.org_code               = b.org_code,\n" +
                "        a.org_name               = b.org_name,\n" +
                "        a.profitcenter_code      = b.profit_center_code,\n" +
                "        a.profitcenter_name      = b.profit_center_name,\n" +
                "        a.supply_code            = b.supply_code,\n" +
                "        a.cust_name              = b.channel_name\n" +
                "    where b.account_period = 202405";
        Connection connection = DmUtil.connection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.executeUpdate();
    }

}
